package com.prj.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;

/**
 * 单元格取消合并并添加颜色 
 */
public class ExcelUnmergeFillAndHighlight {
    
    public static void main(String[] args) {
        String inputFilePath = "C:\\Users\\keven\\Desktop\\result.xlsx";
        String outputFilePath = "C:\\Users\\keven\\Desktop\\名单表2.xlsx";
        
        try (FileInputStream fis = new FileInputStream(inputFilePath);
             Workbook workbook = WorkbookFactory.create(fis);
             FileOutputStream fos = new FileOutputStream(outputFilePath)) {
            
            // 创建红色填充样式
            CellStyle redFillStyle = workbook.createCellStyle();
            redFillStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            redFillStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            redFillStyle.setBorderBottom(BorderStyle.THIN);
            redFillStyle.setBorderTop(BorderStyle.THIN);
            redFillStyle.setBorderLeft(BorderStyle.THIN);
            redFillStyle.setBorderRight(BorderStyle.THIN);
            
            // 处理所有工作表
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                unmergeFillAndHighlight(sheet, redFillStyle);
            }
            
            // 保存修改后的工作簿
            workbook.write(fos);
            System.out.println("处理完成！输出文件: " + outputFilePath);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    private static void unmergeFillAndHighlight(Sheet sheet, CellStyle highlightStyle) {
        // 1. 获取所有合并区域（从后向前遍历）
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            
            // 2. 获取合并区域左上角单元格的值
            Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
            Cell firstCell = firstRow == null ? null : firstRow.getCell(mergedRegion.getFirstColumn());
            Object cellValue = getCellValue(firstCell);
            
            // 3. 取消合并区域
            sheet.removeMergedRegion(i);
            
            // 4. 在原始合并区域内填充相同值并设置红色背景
            for (int rowIdx = mergedRegion.getFirstRow(); rowIdx <= mergedRegion.getLastRow(); rowIdx++) {
                Row row = sheet.getRow(rowIdx);
                if (row == null) {
                    row = sheet.createRow(rowIdx);
                }
                
                for (int colIdx = mergedRegion.getFirstColumn(); colIdx <= mergedRegion.getLastColumn(); colIdx++) {
                    Cell cell = row.getCell(colIdx);
                    if (cell == null) {
                        cell = row.createCell(colIdx);
                    }
                    
                    // 设置单元格值
                    setCellValue(cell, cellValue);
                    
                    // 应用红色背景样式（保留原有样式的基础上添加背景色）
                    CellStyle newStyle = mergeStyles(cell, highlightStyle, sheet.getWorkbook());
                    cell.setCellStyle(newStyle);
                }
            }
        }
    }
    
    // 合并原有样式和红色背景样式
    private static CellStyle mergeStyles(Cell cell, CellStyle highlightStyle, Workbook workbook) {
        CellStyle newStyle = workbook.createCellStyle();
        
        // 如果单元格已有样式，则复制原有样式
        if (cell.getCellStyle() != null) {
            newStyle.cloneStyleFrom(cell.getCellStyle());
        }
        
        // 应用红色背景
        newStyle.setFillForegroundColor(highlightStyle.getFillForegroundColor());
        newStyle.setFillPattern(highlightStyle.getFillPattern());
        
        return newStyle;
    }
    
    private static Object getCellValue(Cell cell) {
        if (cell == null) return "";
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return cell.getNumericCellValue();
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
    
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) return;
        
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof String && ((String) value).startsWith("=")) {
            cell.setCellFormula(((String) value).substring(1));
        }
    }
}